/**
 * ============================================
 * 卡片管理路由模块 - card.js
 *
 * 功能说明：
 * - 提供完整的虚拟卡管理系统API接口
 * - 包含卡列表查询、卡创建、卡详情、持卡人管理等完整功能
 * - 支持VISA、MasterCard、Discover等卡组织
 * - 实现卡充值、卡历史查询、资金明细等金融操作
 *
 * 核心功能模块：
 * - 卡管理：卡的创建、查询、编辑、充值等操作
 * - 持卡人管理：持卡人的创建、编辑、查询等操作
 * - 交易记录：卡的交易历史和资金明细查询
 * - 卡BIN管理：卡号前缀和卡组织信息管理
 *
 * 安全特性：
 * - 所有接口都需要JWT认证
 * - 用户只能操作自己的卡和持卡人数据
 * - 卡号敏感信息进行掩码处理
 * - 参数验证和权限检查完善
 *
 * 路由前缀：/api/card（在app.js中注册时指定）
 * 示例接口：GET /api/card/cards, POST /api/card/cards
 *
 * 响应格式：
 * - 成功：{status: 0, msg: '成功消息', data: 数据}
 * - 失败：{status: 1, msg: '错误消息', data: null}
 * ============================================
 */

// 引入Express框架 - 用于创建路由和处理HTTP请求
const express = require('express');
// 引入express-validator库 - 用于请求参数验证和错误处理
const { body, validationResult } = require('express-validator');
// 引入数据库操作函数 - 从database配置模块导入常用的数据库操作
const { query, get, insert, update } = require('../config/database');
// 引入认证中间件 - 确保所有卡管理操作都需要用户登录
const { authenticateToken } = require('../middlewares/auth');

/**
 * 创建Express路由实例
 * 功能：定义卡片管理相关的所有API端点
 * 在app.js中通过app.use('/api/card', router)注册此路由
 */
const router = express.Router();

/**
 * ====================
 * 统一的卡管理GET接口
 * ====================
 * 接口：GET /cards
 * 功能：通过_action和_subAction参数区分不同操作，实现多功能统一接口
 * 特点：单一接口支持多种查询操作，减少API数量，提高代码复用性
 * 认证：需要JWT token认证，确保用户只能访问自己的数据
 *
 * 支持的操作类型：
 * - 默认操作：获取卡列表（带分页和过滤条件）
 * - getCardFee：获取卡费用信息
 * - queryCardBin/getCarBin：查询卡BIN信息
 * - getCardholder：查询持卡人列表
 *
 * @param {Object} req - Express请求对象，包含查询参数
 * @param {Object} res - Express响应对象，返回对应操作的数据
 * @returns {JSON} 根据操作类型返回不同的数据格式
 */
router.get('/cards', authenticateToken, async (req, res) => {
  try {
    /**
     * 获取查询参数
     * - _action: 主操作类型（getData等）
     * - _subAction: 子操作类型（getCardFee、queryCardBin等）
     */
    const { _action, _subAction } = req.query;

    /**
     * 操作1：获取卡列表（默认操作或getData操作）
     * 功能：查询用户的所有卡信息，支持分页和多种过滤条件
     * 使用场景：卡管理页面显示卡列表
     */
    if ((!_action && !_subAction) || (_action === 'getData' && !_subAction)) {
      /**
       * 获取查询参数
       * - page: 页码，默认1
       * - perPage: 每页数量，默认10
       * - createdAtStart/createdAtEnd: 创建时间范围过滤
       * - cardType/cardFormFactor/cardStatus: 卡类型、形式、状态过滤
       */
      const {
        page = 1,
        perPage = 10,
        createdAtStart,
        createdAtEnd,
        cardType,
        cardFormFactor,
        cardStatus
      } = req.query;

      const userId = req.user.userId; // 从JWT token获取用户ID
      const offset = (page - 1) * perPage; // 计算分页偏移量

      /**
       * 构建动态查询条件
       * 功能：根据传入的过滤条件动态生成SQL WHERE子句
       * 安全性：始终包含用户ID条件，确保数据隔离
       */
      let whereConditions = ['c.user_id = ?']; // 基础条件：用户ID匹配
      let queryParams = [userId]; // 查询参数数组

      // 卡类型过滤
      if (cardType) {
        whereConditions.push('c.card_type = ?');
        queryParams.push(cardType);
      }

      // 卡形式过滤（物理卡/虚拟卡）
      if (cardFormFactor) {
        whereConditions.push('c.card_form_factor = ?');
        queryParams.push(cardFormFactor);
      }

      // 卡状态过滤（激活/冻结等）
      if (cardStatus) {
        whereConditions.push('c.card_status = ?');
        queryParams.push(cardStatus);
      }

      // 创建时间范围过滤（开始日期）
      if (createdAtStart) {
        whereConditions.push('DATE(c.created_at) >= ?');
        queryParams.push(createdAtStart);
      }

      // 创建时间范围过滤（结束日期）
      if (createdAtEnd) {
        whereConditions.push('DATE(c.created_at) <= ?');
        queryParams.push(createdAtEnd);
      }

      // 构建完整的WHERE子句
      const whereClause = whereConditions.length > 0 ? `WHERE ${whereConditions.join(' AND ')}` : '';

      /**
       * 获取卡总数（用于分页）
       * 功能：计算满足条件的卡总数，用于前端分页显示
       */
      const totalResult = await query(
        `SELECT COUNT(*) as total FROM cards c ${whereClause}`,
        queryParams
      );
      const total = totalResult[0].total;

      /**
       * 获取分页卡数据
       * 功能：查询当前页的卡数据，关联持卡人信息
       * SQL说明：
       * - LEFT JOIN cardholders: 关联持卡人表获取持卡人姓名和邮箱
       * - ORDER BY c.created_at DESC: 按创建时间倒序排列
       * - LIMIT ? OFFSET ?: 分页限制
       */
      const cards = await query(
        `SELECT c.*, ch.first_name, ch.last_name, ch.email
         FROM cards c
         LEFT JOIN cardholders ch ON c.cardholder_id = ch.id
         ${whereClause}
         ORDER BY c.created_at DESC
         LIMIT ? OFFSET ?`,
        [...queryParams, parseInt(perPage), offset]
      );

      /**
       * 格式化卡数据，适配前端表格字段名
       * 功能：将数据库字段名转换为前端期望的字段名
       * 安全性：对卡号进行掩码处理，隐藏中间8位数字
       */
      const formattedCards = cards.map(card => ({
        id: card.id, // 卡ID
        created_at: card.created_at, // 创建时间
        card_json: {
          cardId: card.id, // 卡ID（重复字段，前端需要）
          maskCardNo: card.card_number.replace(/(\d{4})\d{8}(\d{4})/, '$1********$2'), // 掩码卡号
          cardNo: card.card_number, // 完整卡号（谨慎使用）
          cardScheme: card.card_scheme, // 卡组织（VISA/MasterCard等）
          cardFormFactor: card.card_form_factor, // 卡形式（physical/virtual）
          cardType: card.card_type, // 卡类型（debit/credit等）
          nickname: card.nickname, // 卡昵称
          cardBalance: card.balance, // 卡余额
          cardCurrency: card.currency, // 卡币种
          cardStatus: card.card_status, // 卡状态
          firstName: card.first_name, // 持卡人名
          lastName: card.last_name, // 持卡人姓
          email: card.email // 持卡人邮箱
        },
        cardholder: `${card.first_name} ${card.last_name}`, // 持卡人全名
        cardFormFactor: card.card_form_factor, // 卡形式
        cardType: card.card_type, // 卡类型
        nickname: card.nickname, // 卡昵称
        cardBalance: card.balance, // 卡余额
        cardStatus: card.card_status // 卡状态
      }));

      /**
       * 返回卡列表数据
       * 格式：包含分页信息的标准响应格式
       */
      return res.json({
        status: 0,
        msg: '获取卡列表成功',
        data: {
          items: formattedCards, // 当前页的卡数据
          total: total // 总卡数（用于分页）
        }
      });
    }

    /**
     * 操作2：获取卡费用
     * 功能：返回卡的固定费用信息（开卡费、服务费等）
     * 使用场景：开卡时显示费用明细
     */
    else if (_action === 'getData' && _subAction === 'getCardFee') {
      /**
       * 卡费用配置（硬编码，实际项目中可从数据库或配置文件中读取）
       * - card_opening_fee: 开卡费
       * - card_service_fee: 服务费
       * - available_balance: 可用余额（示例数据）
       */
      const cardFees = {
        card_opening_fee: '10.00',
        card_service_fee: '5.00',
        available_balance: '1000.00'
      };

      return res.json({
        status: 0,
        msg: '获取卡费用成功',
        data: cardFees
      });
    }

    /**
     * 操作3：查询卡BIN
     * 功能：根据卡组织查询可用的卡BIN（Bank Identification Number）
     * 使用场景：开卡时选择卡BIN
     * 安全性：只允许查询VISA、MasterCard、Discover三个卡组织
     */
    else if (_action === 'getData' && (_subAction === 'queryCardBin' || _subAction === 'getCarBin')) {
      const { cardScheme } = req.query; // 卡组织参数
      
      /**
       * 构建查询条件
       * 安全性：限制可查询的卡组织，防止非法查询
       */
      let whereConditions = ['is_active = TRUE']; // 基础条件：只查询激活的BIN
      let queryParams = [];
      
      // 只允许VISA、MasterCard、Discover三个卡组织
      const allowedSchemes = ['VISA', 'MasterCard', 'Discover'];
      
      if (cardScheme && cardScheme !== '') {
        // 如果指定了cardScheme，验证是否在允许的范围内
        if (allowedSchemes.includes(cardScheme)) {
          whereConditions.push('card_scheme = ?');
          queryParams.push(cardScheme);
        } else {
          // 如果是不允许的cardScheme，返回空列表（不暴露错误信息）
          return res.json({
            status: 0,
            msg: '获取卡BIN成功',
            data: {
              options: [] // 返回空选项列表
            }
          });
        }
      } else {
        // 如果没有选择cardScheme，返回所有允许的卡组织的卡BIN
        whereConditions.push('card_scheme IN (?, ?, ?)');
        queryParams.push(...allowedSchemes);
      }
      
      const whereClause = whereConditions.length > 0 ? `WHERE ${whereConditions.join(' AND ')}` : '';
      
      /**
       * 从数据库查询卡BIN数据
       * 功能：查询card_bins表中符合条件的BIN信息
       * 字段映射：bin_value→value, bin_label→label（适配前端下拉框）
       */
      const cardBins = await query(
        `SELECT bin_value as value, bin_label as label, FALSE as disabled
         FROM card_bins
         ${whereClause}
         ORDER BY card_scheme, bin_value`,
        queryParams
      );

      return res.json({
        status: 0,
        msg: '获取卡BIN成功',
        data: {
          options: cardBins // 返回BIN选项列表
        }
      });
    }

    /**
     * 操作4：查询持卡人
     * 功能：查询当前用户的持卡人列表，用于开卡时选择持卡人
     * 使用场景：开卡页面选择持卡人下拉框
     */
    else if (_action === 'getData' && _subAction === 'getCardholder') {
      const userId = req.user.userId;

      /**
       * 查询持卡人列表
       * 条件：属于当前用户且状态为normal（正常）的持卡人
       * 格式：id作为value，姓名+邮箱作为label（适配下拉框）
       */
      const cardHolders = await query(
        `SELECT id as value, CONCAT(first_name, ' ', last_name, ' (', email, ')') as label
         FROM cardholders
         WHERE user_id = ? AND status = 'normal'`,
        [userId]
      );

      return res.json({
        status: 0,
        msg: '获取持卡人成功',
        data: {
          options: cardHolders.length > 0 ? cardHolders : [] // 确保返回数组格式
        }
      });
    }

    /**
     * 默认处理：无效的操作参数
     * 功能：处理未匹配的任何操作参数，返回错误信息
     */
    else {
      res.json({
        status: 1,
        msg: '无效的请求参数',
        data: null
      });
    }

  } catch (error) {
    /**
     * 统一错误处理
     * 功能：捕获所有操作中的异常，返回标准错误响应
     */
    console.error('Cards API error:', error);
    res.status(500).json({
      status: 1,
      msg: '操作失败',
      data: null
    });
  }
});


// ====================
// 创建卡接口
// ====================
router.post('/cards', authenticateToken, [
  body('cardBin').notEmpty().withMessage('卡BIN不能为空'),
  body('cardCurrency').notEmpty().withMessage('卡币种不能为空'),
  body('cardFormFactor').notEmpty().withMessage('卡形式不能为空'),
  body('cardScheme').notEmpty().withMessage('卡方案不能为空'),
  body('cardType').notEmpty().withMessage('卡类型不能为空'),
  body('cardholderId').notEmpty().withMessage('持卡人ID不能为空')
], async (req, res) => {
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.json({
        status: 1,
        msg: errors.array()[0].msg,
        data: null
      });
    }

    const {
      cardBin,
      cardCurrency,
      cardFormFactor,
      cardScheme,
      cardType,
      cardholderId,
      rechargeAmount = 0
    } = req.body;

    const userId = req.user.userId;

    // 验证持卡人是否存在且属于当前用户
    const cardholder = await get(
      'SELECT id FROM cardholders WHERE id = ? AND user_id = ?',
      [cardholderId, userId]
    );

    if (!cardholder) {
      return res.json({
        status: 1,
        msg: '持卡人不存在',
        data: null
      });
    }

    // 映射卡类型和卡形式到数据库枚举值
    const dbCardType = cardType === 'recharge' ? 'debit' : cardType;
    const dbCardFormFactor = cardFormFactor; // 现在前端传递的值直接匹配数据库枚举值

    // 生成卡号
    const lastFour = Math.floor(Math.random() * 10000).toString().padStart(4, '0');
    const cardNumber = `${cardBin}${'0'.repeat(8)}${lastFour}`;

    // 创建卡
    const cardId = await insert(
      `INSERT INTO cards (user_id, cardholder_id, card_number, card_bin, card_type,
       card_form_factor, card_scheme, currency, balance, available_balance, card_status)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'normal')`,
      [userId, cardholderId, cardNumber, cardBin, dbCardType, dbCardFormFactor,
       cardScheme, cardCurrency, rechargeAmount, rechargeAmount]
    );

    // 如果有充值金额，创建资金明细
    if (rechargeAmount > 0) {
      await insert(
        `INSERT INTO money_details (user_id, change_type, amount, balance, description)
         VALUES (?, 1, ?, ?, ?)`,
        [userId, rechargeAmount, rechargeAmount, `开卡充值 ${rechargeAmount} ${cardCurrency}`]
      );
    }

    const newCard = await get(
      'SELECT * FROM cards WHERE id = ?',
      [cardId]
    );

    res.status(201).json({
      status: 0,
      msg: '卡创建成功',
      data: newCard
    });

  } catch (error) {
    console.error('Create card error:', error);
    res.status(500).json({
      status: 1,
      msg: '创建卡失败: ' + error.message,
      data: null
    });
  }
});


// ====================
// 统一的卡管理PUT接口
// 通过_action参数区分不同操作
// ====================
router.put('/cards/:id', authenticateToken, async (req, res) => {
  try {
    const { id } = req.params;
    const { _action } = req.query;

    // 操作1：编辑卡昵称
    if (_action === 'editNickname') {
      const { card_json } = req.body;
      
      if (!card_json || !card_json.nickname) {
        return res.json({
          status: 1,
          msg: '昵称不能为空',
          data: null
        });
      }

      const userId = req.user.userId;

      // 验证卡是否属于当前用户
      const card = await get(
        'SELECT id FROM cards WHERE id = ? AND user_id = ?',
        [id, userId]
      );

      if (!card) {
        return res.status(404).json({
          status: 1,
          msg: '卡不存在',
          data: null
        });
      }

      // 更新卡昵称
      await update(
        'UPDATE cards SET nickname = ? WHERE id = ?',
        [card_json.nickname, id]
      );

      res.json({
        status: 0,
        msg: '卡昵称更新成功',
        data: null
      });
    }
    // 操作2：卡充值
    else if (_action === 'recharge') {
      const { rechargeAmount } = req.body;

      if (!rechargeAmount || rechargeAmount <= 0) {
        return res.json({
          status: 1,
          msg: '充值金额必须大于0',
          data: null
        });
      }

      const userId = req.user.userId;

      // 验证卡是否属于当前用户
      const card = await get(
        'SELECT id, balance, currency FROM cards WHERE id = ? AND user_id = ?',
        [id, userId]
      );

      if (!card) {
        return res.status(404).json({
          status: 1,
          msg: '卡不存在',
          data: null
        });
      }

      const newBalance = parseFloat(card.balance) + parseFloat(rechargeAmount);

      // 更新卡余额
      await update(
        'UPDATE cards SET balance = ?, available_balance = ? WHERE id = ?',
        [newBalance, newBalance, id]
      );

      // 创建资金明细
      await insert(
        `INSERT INTO money_details (user_id, change_type, amount, balance, description)
         VALUES (?, 1, ?, ?, ?)`,
        [userId, rechargeAmount, newBalance, `卡充值 ${rechargeAmount} ${card.currency}`]
      );

      // 创建卡交易记录
      await insert(
        `INSERT INTO card_transactions (card_id, transaction_type, amount, currency, status)
         VALUES (?, 'recharge', ?, ?, 'completed')`,
        [id, rechargeAmount, card.currency]
      );

      res.json({
        status: 0,
        msg: '充值成功',
        data: {
          newBalance: newBalance.toFixed(2)
        }
      });
    } else {
      res.json({
        status: 1,
        msg: '无效的请求参数',
        data: null
      });
    }
  } catch (error) {
    console.error('Card PUT API error:', error);
    res.status(500).json({
      status: 1,
      msg: '操作失败',
      data: null
    });
  }
});

// ====================
// 统一的卡详情GET接口
// 通过_action和_subAction参数区分不同操作
// ====================
router.get('/cards/:id', authenticateToken, async (req, res) => {
  try {
    const { id } = req.params;
    const { _action, _subAction, rechargeAmount, pageIndex = 1, pageSize = 10 } = req.query;

    const userId = req.user.userId;

    // 验证卡是否属于当前用户
    const card = await get(
      'SELECT id FROM cards WHERE id = ? AND user_id = ?',
      [id, userId]
    );

    if (!card) {
      return res.status(404).json({
        status: 1,
        msg: '卡不存在',
        data: null
      });
    }

    // 默认操作：获取卡详情（当没有_action和_subAction参数时）
    if (!_action && !_subAction) {
      const cardDetail = await get(
        `SELECT
          c.*,
          ch.first_name,
          ch.last_name,
          ch.email,
          ch.mobile_prefix,
          ch.mobile,
          ch.nationality_country_code as nationality,
          ch.cardholder_name_abbreviation,
          ch.cardholder_id,
          ch.residential_country_code as billing_country,
          ch.residential_state as billing_state,
          ch.residential_city as billing_city,
          ch.residential_address as billing_address,
          ch.residential_postal_code as billing_postal_code
         FROM cards c
         LEFT JOIN cardholders ch ON c.cardholder_id = ch.id
         WHERE c.id = ? AND c.user_id = ?`,
        [id, userId]
      );

      if (!cardDetail) {
        return res.status(404).json({
          status: 1,
          msg: '卡不存在',
          data: null
        });
      }

      // 生成CVV信息（模拟数据）
      const cvvInfo = {
        cardId: cardDetail.id,
        cardNo: cardDetail.card_number,
        cvv: Math.floor(100 + Math.random() * 900).toString(), // 生成3位随机CVV
        expirationDate: cardDetail.expiration_date || '12/25' // 默认有效期
      };

      // 格式化卡详情数据以匹配前端期望
      const formattedCardDetail = {
        matrixAccount: '',
        cardId: cardDetail.id,
        cardNo: cardDetail.card_number,
        memberId: cardDetail.user_id,
        cardType: cardDetail.card_type,
        cardScheme: cardDetail.card_scheme,
        cardCurrency: cardDetail.currency,
        firstName: cardDetail.first_name,
        lastName: cardDetail.last_name,
        nationality: cardDetail.nationality,
        email: cardDetail.email,
        mobilePrefix: cardDetail.mobile_prefix,
        mobile: cardDetail.mobile,
        transactionLimitType: '',
        cardBalance: cardDetail.balance,
        expirationDate: cardDetail.expiration_date,
        cardStatus: cardDetail.card_status,
        billingCountry: cardDetail.billing_country || '',
        billingState: cardDetail.billing_state || '',
        billingCity: cardDetail.billing_city || '',
        billingAddress: cardDetail.billing_address || '',
        billingPostalCode: cardDetail.billing_postal_code || '',
        billingAddressUpdatable: '',
        maskCardNo: cardDetail.card_number.replace(/(\d{4})\d{8}(\d{4})/, '$1********$2'),
        nickname: cardDetail.nickname || '',
        createdAt: cardDetail.created_at,
        cardFormFactor: cardDetail.card_form_factor,
        cardholderId: cardDetail.cardholder_id,
        cardholderNameAbbreviation: cardDetail.cardholder_name_abbreviation,
        recipientId: '',
        updateAt: cardDetail.updated_at
      };

      res.json({
        status: 0,
        msg: '获取卡详情成功',
        data: {
          cardholderId: cardDetail.cardholder_id,
          cvvInfo: cvvInfo,
          cardDetail: formattedCardDetail
        }
      });
    }
    // 预期转账金额
    else if (_action === 'getData' && _subAction === 'preRecharge') {
      const amount = parseFloat(rechargeAmount) || 0;
      const rechargeFee = amount * 0.01; // 1% 手续费
      const arrivalAmount = amount - rechargeFee;

      res.json({
        status: 0,
        msg: '获取预期转账金额成功',
        data: {
          arrivalAmount: arrivalAmount.toFixed(2),
          rechargeFee: rechargeFee.toFixed(2)
        }
      });
    }
    // 获取卡详情
    else if (_action === 'getData' && _subAction === 'cardDetail') {
      const cardDetail = await get(
        `SELECT
          c.*,
          ch.first_name,
          ch.last_name,
          ch.email,
          ch.mobile_prefix,
          ch.mobile,
          ch.nationality_country_code as nationality,
          ch.cardholder_name_abbreviation,
          ch.cardholder_id,
          ch.residential_country_code as billing_country,
          ch.residential_state as billing_state,
          ch.residential_city as billing_city,
          ch.residential_address as billing_address,
          ch.residential_postal_code as billing_postal_code
         FROM cards c
         LEFT JOIN cardholders ch ON c.cardholder_id = ch.id
         WHERE c.id = ? AND c.user_id = ?`,
        [id, userId]
      );

      if (!cardDetail) {
        return res.status(404).json({
          status: 1,
          msg: '卡不存在',
          data: null
        });
      }

      // 生成CVV信息（模拟数据）
      const cvvInfo = {
        cardId: cardDetail.id,
        cardNo: cardDetail.card_number,
        cvv: Math.floor(100 + Math.random() * 900).toString(), // 生成3位随机CVV
        expirationDate: cardDetail.expiration_date || '12/25' // 默认有效期
      };

      // 格式化卡详情数据以匹配前端期望
      const formattedCardDetail = {
        matrixAccount: '',
        cardId: cardDetail.id,
        cardNo: cardDetail.card_number,
        memberId: cardDetail.user_id,
        cardType: cardDetail.card_type,
        cardScheme: cardDetail.card_scheme,
        cardCurrency: cardDetail.currency,
        firstName: cardDetail.first_name,
        lastName: cardDetail.last_name,
        nationality: cardDetail.nationality,
        email: cardDetail.email,
        mobilePrefix: cardDetail.mobile_prefix,
        mobile: cardDetail.mobile,
        transactionLimitType: '',
        cardBalance: cardDetail.balance,
        expirationDate: cardDetail.expiration_date,
        cardStatus: cardDetail.card_status,
        billingCountry: cardDetail.billing_country || '',
        billingState: cardDetail.billing_state || '',
        billingCity: cardDetail.billing_city || '',
        billingAddress: cardDetail.billing_address || '',
        billingPostalCode: cardDetail.billing_postal_code || '',
        billingAddressUpdatable: '',
        maskCardNo: cardDetail.card_number.replace(/(\d{4})\d{8}(\d{4})/, '$1********$2'),
        nickname: cardDetail.nickname || '',
        createdAt: cardDetail.created_at,
        cardFormFactor: cardDetail.card_form_factor,
        cardholderId: cardDetail.cardholder_id,
        cardholderNameAbbreviation: cardDetail.cardholder_name_abbreviation,
        recipientId: '',
        updateAt: cardDetail.updated_at
      };

      res.json({
        status: 0,
        msg: '获取卡详情成功',
        data: {
          cardholderId: cardDetail.cardholder_id,
          cvvInfo: cvvInfo,
          cardDetail: formattedCardDetail
        }
      });
    }
    // 获取卡历史
    else if (_action === 'getData' && _subAction === 'cardHistory') {
      const offset = (pageIndex - 1) * pageSize;

      // 获取交易记录总数
      const totalResult = await query(
        'SELECT COUNT(*) as total FROM card_transactions WHERE card_id = ?',
        [id]
      );
      const total = totalResult[0].total;

      // 获取分页数据
      const transactions = await query(
        `SELECT * FROM card_transactions
         WHERE card_id = ?
         ORDER BY transaction_date DESC
         LIMIT ? OFFSET ?`,
        [id, parseInt(pageSize), offset]
      );

      res.json({
        status: 0,
        msg: '获取卡历史成功',
        data: {
          list: transactions,
          pagination: {
            page: parseInt(pageIndex),
            perPage: parseInt(pageSize),
            total: total,
            totalPages: Math.ceil(total / pageSize)
          }
        }
      });
    }
    // 获取卡资金明细
    else if (_action === 'getData' && _subAction === 'financialDetails') {
      const offset = (pageIndex - 1) * pageSize;

      // 获取资金明细总数
      const totalResult = await query(
        `SELECT COUNT(*) as total FROM money_details
         WHERE user_id = ? AND description LIKE ?`,
        [userId, `%卡ID:${id}%`]
      );
      const total = totalResult[0].total;

      // 获取分页数据
      const financialDetails = await query(
        `SELECT * FROM money_details
         WHERE user_id = ? AND description LIKE ?
         ORDER BY created_at DESC
         LIMIT ? OFFSET ?`,
        [userId, `%卡ID:${id}%`, parseInt(pageSize), offset]
      );

      res.json({
        status: 0,
        msg: '获取资金明细成功',
        data: {
          list: financialDetails,
          pagination: {
            page: parseInt(pageIndex),
            perPage: parseInt(pageSize),
            total: total,
            totalPages: Math.ceil(total / pageSize)
          }
        }
      });
    }
    // 获取充值卡资金明细（分页）
    else if (_action === 'getData' && _subAction === 'pagingRechargeCardFundsDetail') {
      const offset = (pageIndex - 1) * pageSize;

      // 获取资金明细总数
      const totalResult = await query(
        `SELECT COUNT(*) as total FROM money_details
         WHERE user_id = ? AND description LIKE ?`,
        [userId, `%卡ID:${id}%`]
      );
      const total = totalResult[0].total;

      // 获取分页数据
      const financialDetails = await query(
        `SELECT * FROM money_details
         WHERE user_id = ? AND description LIKE ?
         ORDER BY created_at DESC
         LIMIT ? OFFSET ?`,
        [userId, `%卡ID:${id}%`, parseInt(pageSize), offset]
      );

      res.json({
        status: 0,
        msg: '获取充值卡资金明细成功',
        data: {
          list: financialDetails,
          pagination: {
            page: parseInt(pageIndex),
            perPage: parseInt(pageSize),
            total: total,
            totalPages: Math.ceil(total / pageSize)
          }
        }
      });
    }
    // 获取发卡历史（分页）
    else if (_action === 'getData' && _subAction === 'pagingIssuingHistory') {
      const offset = (pageIndex - 1) * pageSize;

      // 获取发卡历史总数
      const totalResult = await query(
        `SELECT COUNT(*) as total FROM card_transactions
         WHERE card_id = ? AND transaction_type = 'issuing'`,
        [id]
      );
      const total = totalResult[0].total;

      // 获取分页数据
      const issuingHistory = await query(
        `SELECT * FROM card_transactions
         WHERE card_id = ? AND transaction_type = 'issuing'
         ORDER BY transaction_date DESC
         LIMIT ? OFFSET ?`,
        [id, parseInt(pageSize), offset]
      );

      res.json({
        status: 0,
        msg: '获取发卡历史成功',
        data: {
          list: issuingHistory,
          pagination: {
            page: parseInt(pageIndex),
            perPage: parseInt(pageSize),
            total: total,
            totalPages: Math.ceil(total / pageSize)
          }
        }
      });
    }
    // 如果没有匹配的操作
    else {
      res.json({
        status: 1,
        msg: '无效的请求参数',
        data: null
      });
    }
  } catch (error) {
    console.error('Card details API error:', error);
    res.status(500).json({
      status: 1,
      msg: '操作失败',
      data: null
    });
  }
});



// ====================
// 统一的持卡人管理GET接口
// 通过_action和_subAction参数区分不同操作
// ====================
router.get('/cardholders', authenticateToken, async (req, res) => {
  try {
    const { _action, _subAction, page = 1, perPage = 10 } = req.query;

    // 获取持卡人允许的国家列表
    if (_action === 'getData' && _subAction === 'getCountries') {
      const allowedCountries = await query(
        `SELECT c.name as label, c.code as value
         FROM cardholder_allowed_countries cac
         JOIN countries c ON cac.country_code = c.code
         ORDER BY c.name`
      );

      res.json({
        status: 0,
        msg: '获取持卡人允许国家列表成功',
        data: {
          options: allowedCountries
        }
      });
    }
    // 获取持卡人列表（默认操作）
    else if (_action === 'getData') {
      const userId = req.user.userId;
      const offset = (page - 1) * perPage;

      // 获取总数
      const totalResult = await query(
        'SELECT COUNT(*) as total FROM cardholders WHERE user_id = ?',
        [userId]
      );
      const total = totalResult[0].total;

      // 获取分页数据
      const cardholders = await query(
        `SELECT * FROM cardholders
         WHERE user_id = ?
         ORDER BY created_at DESC
         LIMIT ? OFFSET ?`,
        [userId, parseInt(perPage), offset]
      );

      // 格式化返回数据，id与cardholder_json同级，cardholderId在cardholder_json内
      const formattedCardholders = cardholders.map(holder => ({
        id: holder.id,
        cardholder_json: {
          cardholderId: holder.cardholder_id,
          cardholderNameAbbreviation: holder.cardholder_name_abbreviation,
          firstName: holder.first_name,
          lastName: holder.last_name,
          fullName: `${holder.first_name} ${holder.last_name}`,
          email: holder.email,
          mobilePrefix: holder.mobile_prefix,
          mobile: holder.mobile,
          dateOfBirth: holder.date_of_birth,
          nationalityCountryCode: holder.nationality_country_code,
          residentialCountryCode: holder.residential_country_code,
          residentialAddress: holder.residential_address,
          residentialCity: holder.residential_city,
          residentialPostalCode: holder.residential_postal_code,
          residentialState: holder.residential_state,
          status: holder.status,
          createdAt: holder.created_at,
          updatedAt: holder.updated_at
        }
      }));

      res.json({
        status: 0,
        msg: '获取持卡人列表成功',
        data: {
          items: formattedCardholders,
          total: total
        }
      });
    } else {
      res.json({
        status: 1,
        msg: '无效的请求参数',
        data: null
      });
    }
  } catch (error) {
    console.error('Get cardholders error:', error);
    res.status(500).json({
      status: 1,
      msg: '操作失败',
      data: null
    });
  }
});

// ====================
// 创建持卡人接口
// ====================
router.post('/cardholders', authenticateToken, [
  body('cardholder_json.cardholderNameAbbreviation').notEmpty().withMessage('持卡人名称缩写不能为空'),
  body('cardholder_json.email').isEmail().withMessage('邮箱格式不正确'),
  body('cardholder_json.firstName').notEmpty().withMessage('名不能为空'),
  body('cardholder_json.lastName').notEmpty().withMessage('姓不能为空'),
  body('cardholder_json.mobile').notEmpty().withMessage('手机号不能为空')
], async (req, res) => {
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.json({
        status: 1,
        msg: errors.array()[0].msg,
        data: null
      });
    }

    const { cardholder_json } = req.body;
    const userId = req.user.userId;

    // 处理mobilePrefix格式转换（前端传递"86"需要转换为"+86"）
    const mobilePrefix = cardholder_json.mobilePrefix ?
      (cardholder_json.mobilePrefix.startsWith('+') ? cardholder_json.mobilePrefix : `+${cardholder_json.mobilePrefix}`)
      : '+86';

    // 生成随机cardholderId（8位数字）
    const cardholderId = Math.floor(10000000 + Math.random() * 90000000).toString();

    // 创建持卡人，设置status为'pending'
    const id = await insert(
      `INSERT INTO cardholders (
        cardholder_id, user_id, cardholder_name_abbreviation, first_name, last_name,
        first_name_abbr, last_name_abbr, email, mobile_prefix, mobile,
        date_of_birth, nationality_country_code, residential_country_code,
        residential_address, residential_city, residential_postal_code, residential_state,
        status
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        cardholderId,
        userId,
        cardholder_json.cardholderNameAbbreviation,
        cardholder_json.firstName,
        cardholder_json.lastName,
        cardholder_json.firstNameAbbr || cardholder_json.firstName.charAt(0),
        cardholder_json.lastNameAbbr || cardholder_json.lastName.charAt(0),
        cardholder_json.email,
        mobilePrefix,
        cardholder_json.mobile,
        cardholder_json.dateOfBirth,
        cardholder_json.nationalityCountryCode,
        cardholder_json.residentialCountryCode,
        cardholder_json.residentialAddress,
        cardholder_json.residentialCity,
        cardholder_json.residentialPostalCode,
        cardholder_json.residentialState,
        'pending'  // 设置状态为待审核
      ]
    );

    const newCardholder = await get(
      'SELECT * FROM cardholders WHERE id = ?',
      [id]
    );

    res.status(201).json({
      status: 0,
      msg: '持卡人创建成功',
      data: {
        id: id,
        cardholder_json: {
          cardholderId: cardholderId,
          cardholderNameAbbreviation: newCardholder.cardholder_name_abbreviation,
          firstName: newCardholder.first_name,
          lastName: newCardholder.last_name,
          fullName: `${newCardholder.first_name} ${newCardholder.last_name}`,
          email: newCardholder.email,
          mobilePrefix: newCardholder.mobile_prefix,
          mobile: newCardholder.mobile,
          dateOfBirth: newCardholder.date_of_birth,
          nationalityCountryCode: newCardholder.nationality_country_code,
          residentialCountryCode: newCardholder.residential_country_code,
          residentialAddress: newCardholder.residential_address,
          residentialCity: newCardholder.residential_city,
          residentialPostalCode: newCardholder.residential_postal_code,
          residentialState: newCardholder.residential_state,
          status: newCardholder.status,
          createdAt: newCardholder.created_at,
          updatedAt: newCardholder.updated_at
        }
      }
    });

  } catch (error) {
    console.error('Create cardholder error:', error);
    res.status(500).json({
      status: 1,
      msg: '创建持卡人失败',
      data: null
    });
  }
});

// ====================
// 获取持卡人信息接口
// ====================
router.get('/cardholders/:id/edit', authenticateToken, async (req, res) => {
  try {
    const { id } = req.params;
    const { _action } = req.query;

    if (_action === 'getData') {
      const userId = req.user.userId;

      const cardholder = await get(
        'SELECT * FROM cardholders WHERE id = ? AND user_id = ?',
        [id, userId]
      );

      if (!cardholder) {
        return res.status(404).json({
          status: 1,
          msg: '持卡人不存在',
          data: null
        });
      }

      res.json({
        status: 0,
        msg: '获取持卡人信息成功',
        data: {
          cardholder_json: {
            cardholderId: cardholder.cardholder_id,
            cardholderNameAbbreviation: cardholder.cardholder_name_abbreviation,
            firstName: cardholder.first_name,
            lastName: cardholder.last_name,
            fullName: `${cardholder.first_name} ${cardholder.last_name}`,
            email: cardholder.email,
            mobilePrefix: cardholder.mobile_prefix,
            mobile: cardholder.mobile,
            dateOfBirth: cardholder.date_of_birth,
            nationalityCountryCode: cardholder.nationality_country_code,
            residentialCountryCode: cardholder.residential_country_code,
            residentialAddress: cardholder.residential_address,
            residentialCity: cardholder.residential_city,
            residentialPostalCode: cardholder.residential_postal_code,
            residentialState: cardholder.residential_state,
            status: cardholder.status,
            createdAt: cardholder.created_at,
            updatedAt: cardholder.updated_at
          }
        }
      });
    } else {
      res.json({
        status: 1,
        msg: '无效的请求参数',
        data: null
      });
    }
  } catch (error) {
    console.error('Get cardholder data error:', error);
    res.status(500).json({
      status: 1,
      msg: '获取持卡人信息失败',
      data: null
    });
  }
});

// ====================
// 编辑持卡人接口
// ====================
router.put('/cardholders/:id', authenticateToken, [
  body('cardholder_json.cardholderNameAbbreviation').notEmpty().withMessage('持卡人名称缩写不能为空'),
  body('cardholder_json.email').isEmail().withMessage('邮箱格式不正确'),
  body('cardholder_json.firstName').notEmpty().withMessage('名不能为空'),
  body('cardholder_json.lastName').notEmpty().withMessage('姓不能为空')
], async (req, res) => {
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.json({
        status: 1,
        msg: errors.array()[0].msg,
        data: null
      });
    }

    const { id } = req.params;
    const { cardholder_json } = req.body;
    const userId = req.user.userId;

    // 验证持卡人是否属于当前用户
    const cardholder = await get(
      'SELECT id FROM cardholders WHERE id = ? AND user_id = ?',
      [id, userId]
    );

    if (!cardholder) {
      return res.status(404).json({
        status: 1,
        msg: '持卡人不存在',
        data: null
      });
    }

    // 更新持卡人信息
    await update(
      `UPDATE cardholders SET 
       cardholder_name_abbreviation = ?, first_name = ?, last_name = ?,
       first_name_abbr = ?, last_name_abbr = ?, email = ?, mobile_prefix = ?, 
       mobile = ?, date_of_birth = ?, nationality_country_code = ?,
       residential_country_code = ?, residential_address = ?, residential_city = ?,
       residential_postal_code = ?, residential_state = ?
       WHERE id = ?`,
      [
        cardholder_json.cardholderNameAbbreviation,
        cardholder_json.firstName,
        cardholder_json.lastName,
        cardholder_json.firstNameAbbr || cardholder_json.firstName.charAt(0),
        cardholder_json.lastNameAbbr || cardholder_json.lastName.charAt(0),
        cardholder_json.email,
        cardholder_json.mobilePrefix || '+86',
        cardholder_json.mobile,
        cardholder_json.dateOfBirth,
        cardholder_json.nationalityCountryCode,
        cardholder_json.residentialCountryCode,
        cardholder_json.residentialAddress,
        cardholder_json.residentialCity,
        cardholder_json.residentialPostalCode,
        cardholder_json.residentialState,
        id
      ]
    );

    res.json({
      status: 0,
      msg: '持卡人信息更新成功',
      data: null
    });

  } catch (error) {
    console.error('Edit cardholder error:', error);
    res.status(500).json({
      status: 1,
      msg: '编辑持卡人失败',
      data: null
    });
  }
});

// ====================
// 获取持卡人详情接口
// ====================
router.get('/cardholders/:id', authenticateToken, async (req, res) => {
  try {
    const { id } = req.params;
    const { _action } = req.query;

    const userId = req.user.userId;

    // 先尝试通过cardholder_id查询
    let cardholder = await get(
      'SELECT * FROM cardholders WHERE cardholder_id = ? AND user_id = ?',
      [id, userId]
    );

    // 如果找不到，再通过id查询
    if (!cardholder) {
      cardholder = await get(
        'SELECT * FROM cardholders WHERE id = ? AND user_id = ?',
        [id, userId]
      );
    }

    if (!cardholder) {
      return res.status(404).json({
        status: 1,
        msg: '持卡人不存在',
        data: null
      });
    }

    // 默认操作：获取持卡人详情（当没有_action参数时）
    if (!_action) {
      res.json({
        status: 0,
        msg: '获取持卡人详情成功',
        data: {
          cardholder_json: {
            cardholderId: cardholder.cardholder_id,
            cardholderNameAbbreviation: cardholder.cardholder_name_abbreviation,
            firstName: cardholder.first_name,
            lastName: cardholder.last_name,
            fullName: `${cardholder.first_name} ${cardholder.last_name}`,
            email: cardholder.email,
            mobilePrefix: cardholder.mobile_prefix,
            mobile: cardholder.mobile,
            dateOfBirth: cardholder.date_of_birth,
            nationalityCountryCode: cardholder.nationality_country_code,
            residentialCountryCode: cardholder.residential_country_code,
            residentialAddress: cardholder.residential_address,
            residentialCity: cardholder.residential_city,
            residentialPostalCode: cardholder.residential_postal_code,
            residentialState: cardholder.residential_state,
            status: cardholder.status,
            createdAt: cardholder.created_at,
            updatedAt: cardholder.updated_at
          }
        }
      });
    }
    // 带参数的版本
    else if (_action === 'getData') {
      res.json({
        status: 0,
        msg: '获取持卡人详情成功',
        data: {
          cardholder_json: {
            cardholderId: cardholder.cardholder_id,
            cardholderNameAbbreviation: cardholder.cardholder_name_abbreviation,
            firstName: cardholder.first_name,
            lastName: cardholder.last_name,
            fullName: `${cardholder.first_name} ${cardholder.last_name}`,
            email: cardholder.email,
            mobilePrefix: cardholder.mobile_prefix,
            mobile: cardholder.mobile,
            dateOfBirth: cardholder.date_of_birth,
            nationalityCountryCode: cardholder.nationality_country_code,
            residentialCountryCode: cardholder.residential_country_code,
            residentialAddress: cardholder.residential_address,
            residentialCity: cardholder.residential_city,
            residentialPostalCode: cardholder.residential_postal_code,
            residentialState: cardholder.residential_state,
            status: cardholder.status,
            createdAt: cardholder.created_at,
            updatedAt: cardholder.updated_at
          }
        }
      });
    } else {
      res.json({
        status: 1,
        msg: '无效的请求参数',
        data: null
      });
    }
  } catch (error) {
    console.error('Get holder details error:', error);
    res.status(500).json({
      status: 1,
      msg: '获取持卡人详情失败',
      data: null
    });
  }
});


module.exports = router;